package org.streets.commons.util;

import java.io.PrintStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import javax.persistence.PersistenceException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.streets.commons.error.NestedRuntimeException;

/**
 * Generic utility methods for working with JDBC. Mainly for internal use within
 * the framework, but also useful for custom JDBC access code.
 * 
 * @author dzb
 */
public abstract class JDBCUtils {

	/**
	 * Constant that indicates an unknown (or unspecified) SQL type.
	 * 
	 * @see java.sql.Types
	 */
	public static final int TYPE_UNKNOWN = Integer.MIN_VALUE;

	private static final Log logger = LogFactory.getLog(JDBCUtils.class);

	public static void cleanup(Connection conn, Statement stmt, ResultSet rs) {

		if (rs != null) {
			close(rs);
		}

		if (stmt != null) {
			close(stmt);
		}

		if (conn != null) {
			close(conn);
		}
	}

	/**
	 * Close the given JDBC Connection and ignore any thrown exception. This is
	 * useful for typical finally blocks in manual JDBC code.
	 * 
	 * @param conn
	 *            the JDBC Connection to close (may be <code>null</code>)
	 */
	public static void close(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
				logger.debug("JDBC Connection closed successfully.");
			} catch (SQLException ex) {
				logger.warn("Could not close JDBC Connection", ex);
			} catch (Throwable ex) {
				// We don't trust the JDBC driver: It might throw
				// RuntimeException or Error.
				logger.warn("Unexpected exception on closing JDBC Connection", ex);
			}
		}
	}

	/**
	 * Close the given JDBC Statement and ignore any thrown exception. This is
	 * useful for typical finally blocks in manual JDBC code.
	 * 
	 * @param stmt
	 *            the JDBC Statement to close (may be <code>null</code>)
	 */
	public static void close(Statement stmt) {
		if (stmt == null)
			return;
		try {
			//if (!stmt.isClosed()) {
			stmt.close();
			//}
		} catch (SQLException ex) {
			logger.debug("Could not close JDBC Statement", ex);
		} catch (Throwable ex) {
			// We don't trust the JDBC driver: It might throw RuntimeException
			// or Error.
			logger.debug("Unexpected exception on closing JDBC Statement", ex);
		}
	}

	/**
	 * Close the given JDBC ResultSet and ignore any thrown exception. This is
	 * useful for typical finally blocks in manual JDBC code.
	 * 
	 * @param rs
	 *            the JDBC ResultSet to close (may be <code>null</code>)
	 */
	public static void close(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException ex) {
				logger.debug("Could not close JDBC ResultSet", ex);
			} catch (Throwable ex) {
				// We don't trust the JDBC driver: It might throw
				// RuntimeException or Error.
				logger.debug("Unexpected exception on closing JDBC ResultSet",
						ex);
			}
		}
	}

	/**
	 * 记录详细的SQLException信息
	 * 
	 * @param ex
	 * @param logger
	 */
	public static void logError(SQLException ex, Log logger) {

		logger.warn("DBERROR OCCURS: \n CODE-" + ex.getErrorCode()
				+ "\n STATE: " + ex.getSQLState() + "  MESSAGE: "
				+ ex.getMessage());
	}
	
	public static void logError(String message, SQLException ex) {

		logger.warn("DBERROR OCCURS: \n CODE-" + ex.getErrorCode()
				+ "\n STATE: " + ex.getSQLState() + "  MESSAGE: "
				+ ex.getMessage());
	}

	/**
	 * Closes the <code>ResultSet</code>, then the <code>Statement</code> or
	 * <code>PreparedStatement</code>, and finally the <code>Connection</code>
	 * in the same sequence as mentioned here. If any of the parameters are null
	 * they will be ignored (not attempted closed).
	 * 
	 * @param connection
	 *            The <code>Connection</code> to close.
	 * @param statement
	 *            The <code>Statement</code> or <code>PreparedStatement</code>
	 *            to close.
	 * @param result
	 *            The <code>ResultSet</code> to close.
	 * @throws PersistenceException
	 *             If one or more SQLExceptions are thrown when closing the
	 *             result set, statement or connection. The error messages from
	 *             all thrown exceptions are collected and included in the one
	 *             PersistenceException that is thrown.
	 */
	public static void close(Connection connection, Statement statement,
			ResultSet result) throws NestedRuntimeException {

		StringBuffer errorText = new StringBuffer(250);

		try {
			if (result != null) {
				result.close();
			}
		} catch (SQLException e) {
			errorText.append("Error: Could not close ResultSet: "+ e.toString() + "\n");
		}

		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			errorText.append("Error: Could not close Statement: "+ e.toString() + "\n");
		}

		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			errorText.append("Error: Could not close Connection: "+ e.toString() + "\n");
		}

		if (errorText.length() > 0) {
			throw new NestedRuntimeException(errorText.toString());
		}
	}

	/**
	 * Inserts all parameters in the array into the
	 * <code>PreparedStatement</code> instance in the sequence their are located
	 * in the array.
	 * 
	 * @param ps
	 *            The <code>PreparedStatement</code> to insert the parameters
	 *            into
	 * @param parameters
	 *            The parameters to insert.
	 * @throws PersistenceException
	 *             If anything goes wrong during the insertion of the
	 *             parameters.
	 */
	public static void bindParameters(PreparedStatement ps, Object... parameters)	
		throws SQLException {
		
		if (parameters == null) return;
		
		for (int i = 0; i < parameters.length; i++) {
			if (parameters[i] != null) {
				ps.setObject(i+1, parameters[i]);
			} else {
				ps.setNull(i+1, Types.NULL);
			}
		}
	}
	
	public static void bindParameters(PreparedStatement ps, Object[] parameters, int[] types) 
		throws SQLException {
		
		if (parameters == null || types == null ) return;
		
		if (parameters.length > types.length) {
			throw new SQLException("Parameters count can not bigger than parameter types array length");
		}
		
		for (int i = 0; i < parameters.length; i++) {
			if (parameters[i] != null) {
				ps.setObject(i+1, parameters[i], types[i]);
			} else {
				ps.setNull(i+1, types[i]);
			}
		}		
	}
	

	/**
	 * 打印�?个数据集到Writer�?
	 * 
	 * @param rs
	 */
	public static void printResultSet(ResultSet rs, PrintStream writer) {
		try {
			ResultSetMetaData rsMeta = rs.getMetaData();
			int colCount = rsMeta.getColumnCount();
			int count = 0;
			while (rs.next()) {
				writer
						.append(count++
								+ "-------------------------------------------------------\r\n");
				for (int i = 1; i <= colCount; i++) {
					writer.append(rsMeta.getColumnLabel(i) + " : " + "\t\t\t"
							+ rs.getString(i) + "\r\n");
				}
			}
			writer
					.append("------------------------------------------------------\r\n");
		} catch (SQLException ex) {

			writer.append("***************************************\r\n");
			writer.append("* SQLException in outputResultSet: "
					+ ex.getMessage() + "\r\n");
			writer.append("* SQLState : " + ex.getSQLState() + "\r\n");
			writer.append("* SQL ERROR CODE " + ex.getErrorCode() + "\r\n");
			writer.append("***************************************\r\n");

		}
		writer.flush();
	}

	/**
	 * 打印�?个ResultSet到字符串
	 * 
	 * @param rs
	 * @return
	 */
	public static String printResultSet(ResultSet rs) {
		StringBuilder sb = new StringBuilder();
		try {
			ResultSetMetaData rsMeta = rs.getMetaData();
			int colCount = rsMeta.getColumnCount();
			int count = 0;
			while (rs.next()) {
				sb
						.append(count++
								+ "-------------------------------------------------------\r\n");
				for (int i = 1; i <= colCount; i++) {
					sb.append(rsMeta.getColumnLabel(i) + " : " + "\t\t\t"
							+ rs.getString(i) + "\r\n");
				}
			}
			sb
					.append("------------------------------------------------------\r\n");
		} catch (SQLException ex) {

			sb.append("***************************************\r\n");
			sb.append("* SQLException in outputResultSet: " + ex.getMessage()
					+ "\r\n");
			sb.append("* SQLState : " + ex.getSQLState() + "\r\n");
			sb.append("* SQL ERROR CODE " + ex.getErrorCode() + "\r\n");
			sb.append("***************************************\r\n");

		}
		return sb.toString();
	}	
}
